﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace GiangDay
{
    class FunctionDung
    {
        private DB data = new DB();
        SqlDataAdapter adap;
        DataTable dt;
        SqlCommand comm;
        #region FrGiangday
        public DataTable loadGiangday()
        {
            adap = new SqlDataAdapter("SELECT TEN_LOP, TEN_MON, NAM_HOC , TEN_HK, TEN_GV, LT, TH, TIET_LT, TIET_TH, SUM_TIET, SOTIET, KHOA.MA_KHOA, MA_HK, MA_LOP, MA_GV, MA_MON,  HOC_LAI, KHOA.TEN_KHOA " +
            
            
                                    "FROM (SELECT DISTINCT TEN_LOP, TEN_MON, TEN_KHOA,  TEN_HK, TEN_GV, LT, TH, " +                                                       
                                                       "CASE WHEN LT=1 THEN (SELECT SO_TIET FROM HOC_PHAN hp WHERE hp.MA_MON=gd.MA_MON and hp.MA_LMH='LT') ELSE null END AS TIET_LT,  "+
                                                       "CASE WHEN TH=1 THEN (SELECT SO_TIET FROM HOC_PHAN hp WHERE hp.MA_MON=gd.MA_MON and hp.MA_LMH='TH') ELSE null END AS TIET_TH,  " +
                                                       "(SELECT SUM(SO_TIET)  AS SUM_TIET FROM HOC_PHAN WHERE MA_MON=gd.MA_MON) AS SUM_TIET, " +
                                                       " SOTIET, gd.MA_KHOA, gd.MA_HK, gd.MA_LOP, gd.MA_GV, gd.MA_MON,  gd.HOC_LAI FROM " +
                                                       
                                   " (select B1.MA_KHOA, B1.MA_HK, B1.MA_LOP,B1.MA_GV, B1.MA_MON, LT, TH, B1.HOC_LAI , sum(B1.SO_TIET_TTE) AS SOTIET from "+
                                   " (select LT, gv.MA_KHOA, gv.MA_HK, gv.MA_LOP, gv.MA_GV, gv.MA_MON, gv.HOC_LAI, gv.SO_TIET_TTE from GIANG_DAY gv "+
                                    "LEFT JOIN (select 1 AS LT,  MA_KHOA, MA_HK,MA_GV, MA_LOP, MA_MON, HOC_LAI from GIANG_DAY WHERE MA_LMH='LT') AS KQ1  "+
                                                        "ON  gv.MA_KHOA=KQ1.MA_KHOA "+
					                                    "and gv.MA_HK=KQ1.MA_HK "+
					                                    "and gv.MA_LOP=KQ1.MA_LOP "+
					                                    "and gv.MA_MON=KQ1.MA_MON "+
					                                    "and gv.HOC_LAI=KQ1.HOC_LAI) AS B1 "+
                                   " LEFT JOIN (select 1 AS TH,  MA_KHOA, MA_GV, MA_HK, MA_LOP, MA_MON, HOC_LAI from GIANG_DAY WHERE MA_LMH='TH') AS KQ1  "+
                                                        "ON  B1.MA_KHOA=KQ1.MA_KHOA "+
					                                    "and B1.MA_HK=KQ1.MA_HK "+
					                                    "and B1.MA_LOP=KQ1.MA_LOP "+
					                                    "and B1.MA_MON=KQ1.MA_MON "+
					                                    "and B1.HOC_LAI=KQ1.HOC_LAI "+
                                   " group by B1.MA_KHOA, B1.MA_HK, B1.MA_LOP, B1.MA_GV, B1.MA_MON, LT, TH, B1.HOC_LAI) gd, MON_HOC mh, GIAO_VIEN gv, HOC_KY hk, KHOA kh, LOP lp "+
                                   " where gd.MA_MON=mh.MA_MON and "+
	                                      "gd.MA_GV=gv.MA_GV and "+
                                          "gd.MA_KHOA=kh.MA_KHOA and " +
	                                      "gd.MA_HK=hk.MA_HK and "+
                                          "gd.MA_LOP=lp.MA_LOP " +
                                    ") AS KG2, KHOA WHERE KG2.MA_KHOA=KHOA.MA_KHOA ORDER BY MA_LOP, MA_MON, MA_KHOA, MA_HK, HOC_LAI", data.conn);
            dt=new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public DataTable timGiangday(string malop, string manam)
        {
            string query = "";
            if (malop!=""&&manam!="")
            {
                query = " AND KG2.MA_LOP='" + malop + "' AND KG2.MA_KHOA='" + manam + "' ";
            }
            else if (malop!="")
            {
                query = " AND KG2.MA_LOP='" + malop + "' ";
            }
            else
            {
                query = "AND KG2.MA_KHOA='" + manam + "' ";
            }
            adap = new SqlDataAdapter("SELECT TEN_LOP, TEN_MON, NAM_HOC , TEN_HK, TEN_GV, LT, TH, TIET_LT, TIET_TH, SUM_TIET, SOTIET, KHOA.MA_KHOA, MA_HK, MA_LOP, MA_GV, MA_MON,  HOC_LAI, KHOA.TEN_KHOA " +


                                      "FROM (SELECT DISTINCT TEN_LOP, TEN_MON, TEN_KHOA,  TEN_HK, TEN_GV, LT, TH, " +
                                                         "CASE WHEN LT=1 THEN (SELECT SO_TIET FROM HOC_PHAN hp WHERE hp.MA_MON=gd.MA_MON and hp.MA_LMH='LT') ELSE null END AS TIET_LT,  " +
                                                         "CASE WHEN TH=1 THEN (SELECT SO_TIET FROM HOC_PHAN hp WHERE hp.MA_MON=gd.MA_MON and hp.MA_LMH='TH') ELSE null END AS TIET_TH,  " +
                                                         "(SELECT SUM(SO_TIET)  AS SUM_TIET FROM HOC_PHAN WHERE MA_MON=gd.MA_MON) AS SUM_TIET, " +
                                                         " SOTIET, gd.MA_KHOA, gd.MA_HK, gd.MA_LOP, gd.MA_GV, gd.MA_MON,  gd.HOC_LAI FROM " +

                                     " (select B1.MA_KHOA, B1.MA_HK, B1.MA_LOP,B1.MA_GV, B1.MA_MON, LT, TH, B1.HOC_LAI , sum(B1.SO_TIET_TTE) AS SOTIET from " +
                                     " (select LT, gv.MA_KHOA, gv.MA_HK, gv.MA_LOP, gv.MA_GV, gv.MA_MON, gv.HOC_LAI, gv.SO_TIET_TTE from GIANG_DAY gv " +
                                      "LEFT JOIN (select 1 AS LT,  MA_KHOA, MA_HK,MA_GV, MA_LOP, MA_MON, HOC_LAI from GIANG_DAY WHERE MA_LMH='LT') AS KQ1  " +
                                                          "ON  gv.MA_KHOA=KQ1.MA_KHOA " +
                                                          "and gv.MA_HK=KQ1.MA_HK " +
                                                          "and gv.MA_LOP=KQ1.MA_LOP " +
                                                          "and gv.MA_MON=KQ1.MA_MON " +
                                                          "and gv.HOC_LAI=KQ1.HOC_LAI) AS B1 " +
                                     " LEFT JOIN (select 1 AS TH,  MA_KHOA, MA_GV, MA_HK, MA_LOP, MA_MON, HOC_LAI from GIANG_DAY WHERE MA_LMH='TH') AS KQ1  " +
                                                          "ON  B1.MA_KHOA=KQ1.MA_KHOA " +
                                                          "and B1.MA_HK=KQ1.MA_HK " +
                                                          "and B1.MA_LOP=KQ1.MA_LOP " +
                                                          "and B1.MA_MON=KQ1.MA_MON " +
                                                          "and B1.HOC_LAI=KQ1.HOC_LAI " +
                                     " group by B1.MA_KHOA, B1.MA_HK, B1.MA_LOP, B1.MA_GV, B1.MA_MON, LT, TH, B1.HOC_LAI) gd, MON_HOC mh, GIAO_VIEN gv, HOC_KY hk, KHOA kh, LOP lp " +
                                     " where gd.MA_MON=mh.MA_MON and " +
                                            "gd.MA_GV=gv.MA_GV and " +
                                            "gd.MA_KHOA=kh.MA_KHOA and " +
                                            "gd.MA_HK=hk.MA_HK and " +
                                            "gd.MA_LOP=lp.MA_LOP " +
                                      ") AS KG2, KHOA WHERE KG2.MA_KHOA=KHOA.MA_KHOA "+ query +" ORDER BY MA_LOP, MA_MON, MA_KHOA, MA_HK, HOC_LAI", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public int soHP(string mamon)//1 Cả 2, 2: lý thuyết, 3: thực hành
        {
            adap = new SqlDataAdapter("SELECT * FROM HOC_PHAN WHERE MA_MON='"+ mamon +"'", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            try
            {
                if (dt.Rows.Count == 2)
                    return 1;
                if (dt.Rows[0]["MA_LMH"].ToString() == "LT")
                    return 2;
                return 3;        
            }
            catch (Exception)
            {
                return 0;
            }
           
              
        }
        public DataTable loadKhoa()
        {
            adap = new SqlDataAdapter("SELECT '' AS MA_KHOA, '' AS DKHOA FROM KHOA UNION SELECT MA_KHOA, TEN_KHOA +' - '+ NAM_HOC AS DKHOA FROM KHOA", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public DataTable loadHocky(string maKhoa)
        {
            adap = new SqlDataAdapter("SELECT '' AS MA_HK, '' AS TEN_HK FROM HOC_KY UNION SELECT MA_HK, TEN_HK + ' - ' + MA_KHOA AS TEN_HK FROM HOC_KY where MA_KHOA='" + maKhoa + "'", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public DataTable loadLop()
        {
            adap = new SqlDataAdapter("SELECT '' AS MA_LOP, '' AS TEN_LOP FROM LOP UNION SELECT MA_LOP, TEN_LOP FROM LOP", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public DataTable loadGiaovien()
        {
            adap = new SqlDataAdapter("SELECT '' AS MA_GV, '' AS TEN_GV FROM GIAO_VIEN UNION SELECT MA_GV, TEN_GV FROM GIAO_VIEN", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public DataTable loadMonhoc()
        {
            adap = new SqlDataAdapter("SELECT '' as MA_MON, '' as TEN_MON FROM MON_HOC UNION SELECT MA_MON, TEN_MON FROM MON_HOC", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        }
        public DataTable loadMonhocHoclai(string malop)
        {
            adap = new SqlDataAdapter("SELECT '' as MA_MON, '' as TEN_MON FROM MON_HOC UNION SELECT DISTINCT mh.MA_MON, TEN_MON FROM MON_HOC mh, GIANG_DAY gd WHERE mh.MA_MON=gd.MA_MON and MA_LOP='"+malop+"' and gd.HOC_LAI=0", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
            return dt;
        } 
        //Kiểm tra tồn tại Lớp
        public bool tontaiLop(string mamon, string manam, string malop, string mahocki, int hoclai)
        {      
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM GIANG_DAY WHERE MA_MON='" + mamon + "' AND MA_LOP='" + malop + "' AND HOC_LAI='"+hoclai+"'", data.conn);
            adap.Fill(dt);          
            if (dt.Rows.Count > 0)
                return true;
            return false;
        }
        public float getHesoLT(string type)        {
           
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter("select * from LOAI_MH where MA_LMH='"+type+"'", data.conn);
            adap.Fill(dt);            
            return Convert.ToSingle(dt.Rows[0]["HE_SO"].ToString());
        }
        public int sotietHP(string maMon, string loaiHP) 
        {           
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter("select * from HOC_PHAN where MA_LMH='" + loaiHP + "' and MA_MON='" + maMon + "'", data.conn);
            adap.Fill(dt);           
            return Convert.ToInt32(dt.Rows[0]["SO_TIET"].ToString());
        }
        public int getSiso(string maLop)
        {           
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter("select * from LOP where MA_LOP='" + maLop + "'", data.conn);
            adap.Fill(dt);           
            return Convert.ToInt32(dt.Rows[0]["SI_SO"].ToString());
        }
        public void themMolop(string mamon, string maKhoa, string MaHK, string malop, string maGv)
        {
            string[] config = loadConfig().Split(';');
            float hesoLT = Convert.ToSingle(config[0]); //Hệ số lý thuyết
            float hesoTH = Convert.ToSingle(config[1]);  //he số thực hành

            int siSo = getSiso(malop);

            float hsSisoLT = 0;
            int hsSisoTH =1 + siSo / Convert.ToInt32(config[6]); //Hệ số nhóm thực hành            
            if (siSo < Convert.ToInt32(config[2]))
                hsSisoLT = Convert.ToInt32(config[3]);//Hệ số sĩ số
            else
            {
                int minSS = Convert.ToInt32(config[2]);
                int tangSS = Convert.ToInt32(config[4]);
                float tangHS = Convert.ToSingle(config[5]);
                hsSisoLT = Convert.ToInt32(config[3]) + Convert.ToSingle(((siSo - minSS) / tangSS) * tangHS + 0.1);

            }          
          
            adap = new SqlDataAdapter("SELECT * FROM HOC_PHAN where MA_MON='" + mamon + "'", data.conn);
            dt = new DataTable();
            adap.Fill(dt);
           
            if (dt.Rows.Count == 2)
            {
                //Thêm lý thuyết và thực hành
                int sotietTTE_LT = Convert.ToInt32(sotietHP(mamon, "LT") * hesoLT * hsSisoLT);
                int sotietTTE_TH = Convert.ToInt32(sotietHP(mamon, "TH") * hesoTH * hsSisoTH);   
               
                data.Open();
                comm = new SqlCommand("INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'LT', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 0, " + hesoLT + ", " + sotietTTE_LT + "); "+
                                      "INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'TH', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 0, " + hesoTH + ", " + sotietTTE_TH + ")", data.conn);
                comm.ExecuteNonQuery();
                data.Close();

            }
            else if (dt.Rows[0]["MA_LMH"].ToString().Trim() == "LT")
            {
                int sotietTTE_LT = Convert.ToInt32(sotietHP(mamon, "LT") * hesoLT * hsSisoLT);
                data.Open();
                comm = new SqlCommand("INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'LT', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 0, " + hesoLT + ", " + sotietTTE_LT + ")", data.conn);
                comm.ExecuteNonQuery();
                data.Close();
            }
            else
            {
                int sotietTTE_TH = Convert.ToInt32(sotietHP(mamon, "TH") * hesoTH * hsSisoTH); 
                data.Open();
                comm = new SqlCommand("INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'TH', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 0, " + hesoTH + ", " + sotietTTE_TH + ")", data.conn);
                comm.ExecuteNonQuery();
                data.Close();
                return;
            }             
        }
        public void themMolopHocLai(string mamon, string maKhoa, string MaHK, string malop, string maGv, int siSo, int type)
        {
            string[] config = loadConfig().Split(';');
            float hesoLT = Convert.ToSingle(config[0]); //Hệ số lý thuyết
            float hesoTH = Convert.ToSingle(config[1]);  //he số thực hành
         
            float hsSisoLT = 0;
            int hsSisoTH = 1 + siSo / Convert.ToInt32(config[6]); //Hệ số nhóm thực hành            
            if (siSo < Convert.ToInt32(config[2]))
                hsSisoLT = Convert.ToInt32(config[3]);//Hệ số sĩ số
            else
            {
                int minSS = Convert.ToInt32(config[2]);
                int tangSS = Convert.ToInt32(config[4]);
                float tangHS = Convert.ToSingle(config[5]);
                hsSisoLT = Convert.ToInt32(config[3]) + Convert.ToSingle(((siSo - minSS) / tangSS) * tangHS + 0.1);

            }
          
            if (type == 1)
            {               
                //Thêm lý thuyết và thực hành
                int sotietTTE_LT = Convert.ToInt32(sotietHP(mamon, "LT") * hesoLT * hsSisoLT);
                int sotietTTE_TH = Convert.ToInt32(sotietHP(mamon, "TH") * hesoTH * hsSisoTH);

                data.Open();
                comm = new SqlCommand("INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'LT', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 1, " + hesoLT + ", " + sotietTTE_LT + "); " +
                                      "INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'TH', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 1, " + hesoTH + ", " + sotietTTE_TH + ")", data.conn);
                comm.ExecuteNonQuery();
                data.Close();

            }
            if (type==2)
            {               
                int sotietTTE_LT = Convert.ToInt32(sotietHP(mamon, "LT") * hesoLT * hsSisoLT);
                data.Open();
                comm = new SqlCommand("INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'LT', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 1, " + hesoLT + ", " + sotietTTE_LT + ")", data.conn);
                comm.ExecuteNonQuery();
                data.Close();
            }
            if(type==3)
            {                
                int sotietTTE_TH = Convert.ToInt32(sotietHP(mamon, "TH") * hesoTH * hsSisoTH);
                data.Open();
                comm = new SqlCommand("INSERT INTO GIANG_DAY(MA_MON, MA_LMH, MA_KHOA, MA_HK, MA_LOP, SI_SO, MA_GV, HOC_LAI, HE_SO_SV, SO_TIET_TTE) VALUES('" + mamon + "', 'TH', '" + maKhoa + "' , '" + MaHK + "', '" + malop + "', " + siSo + ", '" + maGv + "', 1, " + hesoTH + ", " + sotietTTE_TH + ")", data.conn);
                comm.ExecuteNonQuery();
                data.Close();               
            }

        }
        public bool xoaGiangday(string khoa, string hocky, string lop ,string monhoc, int hoclai)
        {            
            data.Open();           
            comm = new SqlCommand("DELETE FROM GIANG_DAY WHERE MA_KHOA='" + khoa + "' AND MA_HK='" + hocky + "' AND MA_MON='" + monhoc + "'AND MA_LOP='"+ lop+"' AND HOC_LAI=" + hoclai, data.conn);
            try
            {
                comm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                data.Close();
                return false;
            }
            data.Close();
            return true;
        }
        #endregion

        #region Config
        public string loadConfig()
        {

            adap = new SqlDataAdapter("SELECT (SELECT CAST(HE_SO as varchar(255)) FROM LOAI_MH WHERE MA_LMH='LT')+';'+(SELECT CAST(HE_SO as varchar(255)) FROM LOAI_MH WHERE MA_LMH='TH')+';'+VALUE_CH AS VALUE_CH  FROM CAU_HINH WHERE MA_CH='hesogday'", data.conn);
            dt = new DataTable();
            adap.Fill(dt);            
            return dt.Rows[0]["VALUE_CH"].ToString();
        }
        public bool luuConfig(string hsLT, string hsTH, string LT1, string LT2, string LT3, string LT4, string soNhom)
        {
            data.Open();
            comm = new SqlCommand("UPDATE CAU_HINH SET VALUE_CH='" + LT1 + ";" + LT2 + ";" + LT3 + ";" + LT4 + ";" + soNhom + "' WHERE MA_CH='hesogday'; "+
                                  "UPDATE LOAI_MH SET HE_SO=" + Convert.ToSingle(hsLT) + " WHERE MA_LMH='LT' ;"+
                                  "UPDATE LOAI_MH SET HE_SO=" + Convert.ToSingle(hsTH) + " WHERE MA_LMH='TH'", data.conn);
            try
            {
                comm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                data.Close();
                return false;
            }
            data.Close();
            return true;
        }
        #endregion
    }
}
